import java.sql.*;
import javax.servlet.*;
import javax.servlet.http.*;
import java.io.*;
import java.util.*;

public class PaperRcSale extends HttpServlet
{
	public void doGet(HttpServletRequest request,HttpServletResponse response)throws ServletException,IOException
	{
		doPost(request,response);
	}//doGet

	public void doPost(HttpServletRequest request,HttpServletResponse response)throws ServletException,IOException
	{
		RequestDispatcher dispatcher = null;
		try
		{
			int cardid = Integer.parseInt(request.getParameter("cardid"));
		    int count = Integer.parseInt(request.getParameter("count"));
			int outid = Integer.parseInt(request.getParameter("outid"));

			Class.forName("com.mysql.jdbc.Driver").newInstance();
			Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/airtel","root","root");
			Statement stmt = con.createStatement();
			Statement stmtb = con.createStatement();
			Timestamp ts = null;
			String flag = "true";
			int stock1 = 0,id1=0;
			String qry = "select count,id from paperstock where cardType='"+cardid+"'";
			System.out.println("1::::"+qry);
			ResultSet ras=stmtb.executeQuery(qry);
			while(ras.next())
			{
				id1 = ras.getInt(2);
				stock1 = ras.getInt(1);
			}
			String qry1 = "select count from paperstock where cardType='"+cardid+"'";
			System.out.println("2::::"+qry1);
			ResultSet rs=stmt.executeQuery(qry1);
			if(rs.next())
			{
				int id = 0;
				int stock = Integer.parseInt(rs.getString(1));
				if(stock1 < count)
				{
					request.setAttribute("msg", new String("Sorry Paper Rc. Stock not enough to ur requirement,\n Available stock is : "+stock1));    
				}
				else
				{
					stock1 -= count;

					rs = stmt.executeQuery("select max(id) from papersale");
					if(rs.next())
					{
						if( rs.getString(1) != null )
						{
							id = Integer.parseInt(rs.getString(1));
						}
					}
					id++;
			
					ts = new Timestamp( (new java.util.Date()).getTime() );
					String qry2 = "update paperstock set count='"+stock1+"' where cardType='"+cardid+"' and id="+id1+"";
					System.out.println("3+++++++++++"+qry2);
					stmt.executeUpdate(qry2);	
					String qry3 = "insert into papersale values('"+id+"','"+ts+"','"+count+"','"+outid+"','"+cardid+"','"+flag+"')";
					System.out.println("4+++++++++++"+qry3);
					stmt.executeUpdate(qry3);
					request.setAttribute("msg", new String("Paper Stock Updated"));
				}
			}
			else
			{
				request.setAttribute("msg", new String("Sorry Paper Stock not available"));    
			}

				Collection outletVector = new Vector();
				Collection cardtypeVector = new Vector();
				
				rs = stmt.executeQuery("select id,name from outletdetails");
				while(rs.next())
				{
					outletVector.add(rs.getString(1));
					outletVector.add(rs.getString(2));
				}   
				request.setAttribute("outletnames",outletVector);

				rs = stmt.executeQuery("select id,cardType from cardtype where cardType not like 'cd%'");
				while(rs.next())
				{
					cardtypeVector.add(rs.getString(1));
					cardtypeVector.add(rs.getString(2));
				}   
				request.setAttribute("cardtypes",cardtypeVector);
			dispatcher = request.getRequestDispatcher("/jsp/PaperSales.jsp");
			dispatcher.forward(request,response);
		}//try
		catch(Exception e)
		{
            e.printStackTrace();
		}//catch
	}//service
}//class
